# -*- coding: utf-8 -*-

from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData,CHAR,DATE,DECIMAL,BigInteger
# 连接数据库 
engine = create_engine("mysql://root:root@localhost:3306/stock?charset=utf8",encoding="utf-8", echo=True)
# 获取元数据
metadata = MetaData()
# 定义表
user = Table('stocks', metadata,
        Column('id', Integer, primary_key=True),
        Column('code', CHAR(8)),
        Column('name', String(20)),
        Column('industry', String(20)),
        Column('area', String(4)),
        Column('pe', String(20)),
        Column('outstanding', String(20)),
        Column('totals', String(20)),
        Column('totalAssets', String(20)),
        Column('liquidAssets', String(20)),
        Column('fixedAssets', String(20)),
        Column('reserved', String(20)),
        Column('reservedPerShare', String(20)),
        Column('esp', String(20)),
        Column('bvps', String(20)),
        Column('pb', String(20)),
        Column('timeToMarket', String(20)),
        Column('undp', String(20)),
        Column('perundp', String(20)),
        Column('rev', String(20)),
        Column('profit', String(20)),
        Column('gpr', String(20)),
        Column('npr', String(20)),
        Column('holders', String(20)),
    )

stock_data = Table('stock_data', metadata,
        Column('id', Integer, primary_key=True),
        Column('code', CHAR(8)),
        Column('open', DECIMAL(6,2)),
        Column('high', DECIMAL(6,2)),
        Column('close', DECIMAL(6,2)),
        Column('low', DECIMAL(6,2)),
        Column('volume', BigInteger),
        Column('price_change', DECIMAL(4,2)),
        Column('p_change', DECIMAL(4,2)),
        Column('ma5', DECIMAL(7,3)),
        Column('ma10', DECIMAL(7,3)),
        Column('ma20', DECIMAL(7,3)),
        Column('v_ma5', String(20)),
        Column('v_ma10', String(20)),
        Column('v_ma20', String(20)),
        Column('turnover', DECIMAL(4,2)),
        Column('date', DATE),
    )

address = Table('stock_right_data', metadata,
    Column('id', Integer, primary_key=True),
    Column('code', CHAR(8)),
    Column('open', DECIMAL(6,2)),
    Column('high', DECIMAL(6,2)),
    Column('close', DECIMAL(6,2)),
    Column('low', DECIMAL(6,2)),
    Column('volume', BigInteger),
    Column('amount', BigInteger),
    Column('date', DATE),
)
# 创建数据表，如果数据表存在，则忽视
metadata.create_all(engine)
# 获取数据库连接
conn = engine.connect()

